package by.semerikov.dao;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import by.semerikov.entity.ProposalEntity;
import by.semerikov.exceptions.DAOException;

import com.mysql.jdbc.PreparedStatement;

public class ProposalDAO implements IProposalDAO {

	private Connection con;
	PreparedStatement insertStatement;

	public ProposalDAO(){
	}

	public ProposalDAO(Connection con) {
		super();
		this.con = con;
	}
	
	

	public Connection getConnection() {
		return con;
	}

	public void setConnection(Connection con) {
		this.con = con;
	}

	@Override
	public void create(ProposalEntity object) throws DAOException   {
		try {
			PreparedStatement preparedState;
			if(object.getDriverId() != null)
			{
				 preparedState = (PreparedStatement) con
					.prepareStatement("insert into transportsystem.proposal (name,description,proposal.from,proposal.to,cost,state,personal_id) values(?,?,?,?,?,?,?)");
			}
			else
			{
				preparedState = (PreparedStatement) con
						.prepareStatement("insert into transportsystem.proposal (name,description,proposal.from,proposal.to,cost,state) values(?,?,?,?,?,?)");
				
			}
			preparedState.setString(1, object.getName());
			preparedState.setString(2, object.getDescription());
			preparedState.setString(3, object.getFrom());
			preparedState.setString(4, object.getTo());
			preparedState.setString(5, object.getCost());
			preparedState.setString(6, object.getState());
			if(object.getDriverId() != null)
				preparedState.setInt(7, object.getDriverId());
			preparedState.executeUpdate();
		} catch (Exception e) {
			throw new DAOException("exception.dao.proposal.create",e);
		}

	}

	@Override
	public List<ProposalEntity> read(ProposalEntity object) throws DAOException  {
		ArrayList<ProposalEntity> newList = new ArrayList<ProposalEntity>();
		try {
			PreparedStatement preparedState = doReadPreparedStatement(object,
					"SELECT * FROM transportsystem.proposal ");
			ResultSet resultset = preparedState.executeQuery();
			while (resultset.next()) {
				newList.add(new ProposalEntity(Integer.parseInt(resultset.getString("id"))
						, resultset.getString("name")
						, resultset.getString("description")
						, resultset.getString("from")
						, resultset.getString("to")
						, resultset.getString("cost")
						, resultset.getString("state")
						, resultset.getInt("personal_id")));
			}
			return newList;
		} catch (Exception e) {
			e.printStackTrace();
			throw new DAOException("exception.dao.proposal.read",e);
		}
	}

	private PreparedStatement doReadPreparedStatement(ProposalEntity object,
			String begin) 
		throws SQLException {
			String newStr = begin;
			Map<String, Integer> map = new HashMap<String, Integer>();
			int number = 1;
			if (object.getId() != null) {
				newStr += "where  id = ?";
				map.put("id", number);
				number++;
			}
			if (object.getName() != null) {
				if (number == 1)
					newStr += " where ";
				if (number != 1)
					newStr += " And ";
				newStr += " name Like ?";
				map.put("name", number);
				number++;
			}
			if (object.getDescription() != null) {
				if (number == 1)
					newStr += " where ";
				if (number != 1)
					newStr += " And ";
				newStr += " description Like ?";
				map.put("description", number);
				number++;
			}
			if (object.getFrom() != null) {
				if (number == 1)
					newStr += " where ";
				if (number != 1)
					newStr += " And ";
				newStr += " proposal.from Like ?";
				map.put("from", number);
				number++;
			}
			if (object.getTo() != null) {
				if (number == 1)
					newStr += " where ";
				if (number != 1)
					newStr += " And ";
				newStr += " proposal.to Like ?";
				map.put("to", number);
				number++;
			}
			if (object.getCost() != null) {
				if (number == 1)
					newStr += " where ";
				if (number != 1)
					newStr += " And ";
				newStr += " cost Like ?";
				map.put("cost", number);
				number++;
			}
			if (object.getState() != null) {
				if (number == 1)
					newStr += " where ";
				if (number != 1)
					newStr += " And ";
				newStr += " state = ?";
				map.put("state", number);
				number++;
			}
			if (object.getDriverId() != null) {
				if (number == 1)
					newStr += " where ";
				if (number != 1)
					newStr += " And ";
				newStr += " personal_id = ?";
				map.put("personal_id", number);
				number++;
			}
			PreparedStatement newPS = (PreparedStatement) con
					.prepareStatement(newStr);
			if (map.get("id") != null) {
				newPS.setInt(map.get("id"), object.getId());
			}
			if (map.get("name") != null) {
				newPS.setString(map.get("name"), "%" + object.getName() + "%");
			}
			if (map.get("description") != null) {
				newPS.setString(map.get("description"), "%" + object.getDescription() + "%");
			}
			if (map.get("from") != null) {
				newPS.setString(map.get("from"), "%" + object.getFrom() + "%");
			}
			if (map.get("to") != null) {
				newPS.setString(map.get("to"), "%" + object.getTo() + "%");
			}
			if (map.get("cost") != null) {
				newPS.setString(map.get("cost"), "%" + object.getCost() + "%");
			}
			if (map.get("state") != null) {
				newPS.setString(map.get("state"), object.getState());
			}
			if (map.get("personal_id") != null) {
				newPS.setInt(map.get("personal_id"), object.getDriverId()  );
			}
			return newPS;
	}

	@Override
	public void update(ProposalEntity object) throws DAOException {
		try {
			PreparedStatement preparedState = (PreparedStatement) con
					.prepareStatement("UPDATE transportsystem.proposal SET name =?,description=?,proposal.from=?,proposal.to=?,cost=?,state=?,personal_id=?  WHERE id=?");
			preparedState.setString(1, object.getName());
			preparedState.setString(2, object.getDescription());
			preparedState.setString(3, object.getFrom());
			preparedState.setString(4, object.getTo());
			preparedState.setString(5, object.getCost());
			preparedState.setString(6, object.getState());
			preparedState.setInt(7, object.getDriverId());
			preparedState.setInt(8, object.getId());
			preparedState.executeUpdate();
		} catch (Exception e) {
			throw new DAOException("exception.dao.proposal.update",e);
		}
	}

	@Override
	public void delete(ProposalEntity object) throws DAOException {
		try {
			PreparedStatement preparedState = doReadPreparedStatement(object,
				"DELETE FROM transportsystem.proposal ");
			preparedState.executeUpdate();
		} catch (Exception e) {
			throw new DAOException("exception.dao.proposal.delete",e);
		}
		
	}

}
